/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.sun.courier.dal;

import com.sun.courier.dal.DataAccessHelper;
import com.sun.courier.dto.Employee_EmpLoginDTO;
import com.sun.courier.entity.Employee;
import com.sun.courier.entity.EmployeeLogin;
import com.sun.courier.model.EmployeeModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HONG QUAN
 */
public class EmployeeDAL {
    
    EmployeeModel model = new EmployeeModel();
    DataAccessHelper dah = new DataAccessHelper();
    
    public EmployeeDAL(EmployeeModel model){
        this.model = model;
    }

    public EmployeeDAL() {
    }
    
    private void clearElementsVectors()
    {
        model.data.clear();
    }
    
    public void loadEmployee() throws Exception {
//        clearElementsVectors();
        String strQuery = "Select * from Employee";
        ResultSet rs = dah.executeQuery(strQuery);
        
        while (rs.next())
        {
                Vector vRow = new Vector();
//                vRow.add(new Integer(rs.getInt("EmpId")));
                vRow.add(rs.getString("EmployeeName"));
                vRow.add(rs.getString("Address"));
                vRow.add(rs.getString("Email"));
                vRow.add(rs.getString("Phone"));
                vRow.add(rs.getString("Image"));
                vRow.add(rs.getString("Position"));
//                vRow.add(rs.getInt("DepId"));
                model.data.add(vRow);
        }
        model.fireTableDataChanged(); // update lai JTable
    }
    
    public Boolean insertEmployee(Employee e){
        String strQuery = "Insert into Employee values('"+e.getEmployeeName()+",'"+e.getAddress()+",'"+e.getEmail()+"'";
        int result = 0;
        try {
            result = dah.executeUpdate(strQuery);
        } catch (Exception ex) {
            Logger.getLogger(EmployeeDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(result > 0){
            return true;
        }
        return false;
    }
    
    public ArrayList<Employee_EmpLoginDTO> getAllStoreEmployee(){
        String strQuery ="Select Employee.DepId ,Username, EmployeeName, Address, Phone from Employee "
                + "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                    "join Department on Employee.DepId = Department.DepId\n" +
                    "where Role = '3'";
        ArrayList<Employee_EmpLoginDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Employee e = new Employee();
                e.setDepId(rs.getInt("DepId"));
                e.setEmployeeName(rs.getString("EmployeeName"));
                e.setAddress(rs.getString("Address"));
                e.setPhone(rs.getString("Phone"));
                Employee_EmpLoginDTO edto = new Employee_EmpLoginDTO();
                edto.setE(e);
                EmployeeLogin l = new EmployeeLogin();
                l.setUsername(rs.getString("Username"));
                edto.setL(l);
                lst.add(edto);
            }
        } catch (Exception e) {
        }
        
        return lst;
    }
    
    public ArrayList<Employee_EmpLoginDTO> getAllDespatchingEmployee(){
        String strQuery ="Select Employee.DepId ,Username, EmployeeName, Address, Phone from Employee "
                + "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                    "join Department on Employee.DepId = Department.DepId\n" +
                    "where Role = '2'";//DA BO:  or Role = '1'
        ArrayList<Employee_EmpLoginDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Employee e = new Employee();
                e.setDepId(rs.getInt("DepId"));
                e.setEmployeeName(rs.getString("EmployeeName"));
                e.setAddress(rs.getString("Address"));
                e.setPhone(rs.getString("Phone"));
                Employee_EmpLoginDTO edto = new Employee_EmpLoginDTO();
                edto.setE(e);
                EmployeeLogin l = new EmployeeLogin();
                l.setUsername(rs.getString("Username"));
                edto.setL(l);
                lst.add(edto);
            }
        } catch (Exception e) {
        }
        
        return lst;
    }
    
    public ArrayList<Employee_EmpLoginDTO> getAllReceive_ReturnEmployee(){
        String strQuery ="Select Employee.DepId ,Username, EmployeeName, Address, Phone from Employee "
                + "join EmployeeLogin on Employee.EmpId = EmployeeLogin.EmpId\n" +
                    "join Department on Employee.DepId = Department.DepId\n" +
                    "where Role = '1'";//DA BO:  or Role = '1'
        ArrayList<Employee_EmpLoginDTO> lst = new ArrayList<>();
        try {
            ResultSet rs = dah.executeQuery(strQuery);
            while(rs.next()){
                Employee e = new Employee();
                e.setDepId(rs.getInt("DepId"));
                e.setEmployeeName(rs.getString("EmployeeName"));
                e.setAddress(rs.getString("Address"));
                e.setPhone(rs.getString("Phone"));
                Employee_EmpLoginDTO edto = new Employee_EmpLoginDTO();
                edto.setE(e);
                EmployeeLogin l = new EmployeeLogin();
                l.setUsername(rs.getString("Username"));
                edto.setL(l);
                lst.add(edto);
            }
        } catch (Exception e) {
        }
        
        return lst;
    }
    
    //////////////////////////////////////////////////////NAMBH/////////////////////////////////////////
    private final static String INSERT = "insert into Employee(EmployeeName, Address, Email, Phone, Image, Position, DepId) "
            + "values(?, ?, ?, ?, ?, ?, ?)";
    private final static String UPDATE = "update Employee set EmployeeName=?, Address=?, Email=?, Phone=?, Image=?, Position=?, DepId=? "
            + "where EmpId=?";
    private final static String DELETE = "delete Employee where EmpId=?";
    private final static String GET_ALL = "select * from Employee ORDER BY EmpId desc";
    private final static String GET_BY_ID = "select * from Employee where EmpId=?";
    private final static String GET_BY_DEP = "select * from Employee where DepId=?";
    
//    private DataAccessHelper dah = new DataAccessHelper();
    private Connection con;

//    public EmployeeDAL() {
//    }

    public boolean add(Employee obj) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(INSERT);
                    //EmployeeName, Address, Email, Phone, Image, Position, DepId
                    pre.setString(1, obj.getEmployeeName());
                    pre.setString(2, obj.getAddress());
                    pre.setString(3, obj.getEmail());
                    pre.setString(4, obj.getPhone());
                    pre.setString(5, obj.getImage());
                    pre.setString(6, obj.getPosition());
                    pre.setInt(7, obj.getDepId());
                    
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public boolean edit(Employee obj) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(UPDATE);
                    pre.setString(1, obj.getEmployeeName());
                    pre.setString(2, obj.getAddress());
                    pre.setString(3, obj.getEmail());
                    pre.setString(4, obj.getPhone());
                    pre.setString(5, obj.getImage());
                    pre.setString(6, obj.getPosition());
                    pre.setInt(7, obj.getDepId());
                    pre.setInt(8, obj.getEmpId());
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public boolean delete(int empId) {
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(DELETE);
                    pre.setInt(1, empId);
                    int rs = pre.executeUpdate();
                    if (rs > 0) {
                        return true;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return false;
    }

    public ArrayList<Employee> getAll() {
        ArrayList<Employee> objs = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                ResultSet rs = con.prepareStatement(GET_ALL).executeQuery();
                try {
                    while (rs.next()) {
                        //EmployeeName, Address, Email, Phone, Image, Position, DepId
                        Employee obj = new Employee();
                        obj.setEmpId(rs.getInt("EmpId"));
                        obj.setEmployeeName(rs.getString("EmployeeName"));
                        obj.setAddress(rs.getString("Address"));
                        obj.setEmail(rs.getString("Email"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setImage(rs.getString("Image"));
                        obj.setPosition(rs.getString("Position"));
                        obj.setDepId(rs.getInt("DepId"));

                        objs.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return objs;
    }

   public ArrayList<Employee> getById(int EmpId) {
        ArrayList<Employee> items = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(GET_BY_ID);
                    pre.setInt(1, EmpId);
                    ResultSet rs = pre.executeQuery();
                    while (rs.next()) {
                        Employee obj = new Employee();
                        obj.setEmpId(rs.getInt("EmpId"));
                        obj.setEmployeeName(rs.getString("EmployeeName"));
                        obj.setAddress(rs.getString("Address"));
                        obj.setEmail(rs.getString("Email"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setImage(rs.getString("Image"));
                        obj.setPosition(rs.getString("Position"));
                        obj.setDepId(rs.getInt("DepId"));
                        
                        items.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return items;
    }    
    
    public ArrayList<Employee> getByDep(int depId) {
        ArrayList<Employee> items = new ArrayList<>();
        try {
            con = dah.getConnection();
            if (con != null) {
                try {
                    PreparedStatement pre = con.prepareStatement(GET_BY_DEP);
                    pre.setInt(1, depId);
                    ResultSet rs = pre.executeQuery();
                    while (rs.next()) {
                        Employee obj = new Employee();
                        obj.setEmpId(rs.getInt("EmpId"));
                        obj.setEmployeeName(rs.getString("EmployeeName"));
                        obj.setAddress(rs.getString("Address"));
                        obj.setEmail(rs.getString("Email"));
                        obj.setPhone(rs.getString("Phone"));
                        obj.setImage(rs.getString("Image"));
                        obj.setPosition(rs.getString("Position"));
                        obj.setDepId(rs.getInt("DepId"));
                        
                        items.add(obj);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    dah.Close();
                }
            }
        } catch (Exception e) {
        }
        return items;
    }    
}
